/**
 * Copyright(C) 2012 Luvina Software Company
 * TblUserDaoIpml.java, Aug 24, 2012 Ngo Quang Tinh
 */
package net.luvina.manageuser.dao.impl;

import java.util.List;

import org.hibernate.Hibernate;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.transform.Transformers;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.stereotype.Repository;

import net.luvina.manageuser.dao.TblUserDao;
import net.luvina.manageuser.dto.UserDetailDto;
import net.luvina.manageuser.hibernate.MstGroup;
import net.luvina.manageuser.hibernate.TblUser;
import net.luvina.manageuser.hibernate.User;

/**
 * @author Ngo Quang Tinh
 *
 */
@Repository("tblUserDao")
public class TblUserDaoIpml extends GenericDaoImpl<TblUser, Integer> implements TblUserDao{

    /**
     * @param type
     */
    public TblUserDaoIpml(Class<TblUser> type) {
        super(type);
        // TODO Auto-generated constructor stub
    }

    /* (non-Javadoc)
     * @see net.luvina.manageuser.dao.TblUserDao#getGroupList()
     */
    @Override
    @SuppressWarnings("unchecked")
    public List<MstGroup> getGroupList() {
        HibernateCallback callback = new HibernateCallback() {
            public Object doInHibernate(Session session)
                    throws HibernateException {
                Query query = session.createQuery("FROM MstGroup ");
                return (List<MstGroup>) query.list();
            }
        };
        return (List<MstGroup>) getHibernateTemplate().execute(callback);
    }
    /* (non-Javadoc)
     * @see net.luvina.manageuser.dao.TblUserDao#getListUser(int, int, int, java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.lang.String)
     */
    @Override
    public List<UserDetailDto> getListUser(final int offset,final int limit, final int groupId,
            final String fullName,final String sortType,final String sortByFullName,
            final String sortByCodeLevel,final String sortByEndDate) {
        HibernateCallback callback = new HibernateCallback() {
            public Object doInHibernate(Session session)
                    throws HibernateException {
                StringBuilder sqlBuilder = new StringBuilder();
                sqlBuilder.append(" select ");
                sqlBuilder.append(" user_id, login_name, full_name, full_name_kana, email, tel, birthday, group_name, name_level, start_date, end_date ");
                sqlBuilder.append(" FROM  `tbl_user` as ur");
                sqlBuilder.append(" LEFT JOIN `tbl_detail_user_japan` as ur_jp ");
                sqlBuilder.append(" on ur.user_id = ur_jp.user_id LEFT JOIN `mst_japan` as jp ");
                sqlBuilder.append(" on ur_jp.code_level = jp.code_level, `testsql_user`.`mst_group` as gr ");
                sqlBuilder.append(" WHERE ur.group_id = gr.group_id ");

                if (groupId > 0 && (fullName != null && fullName != "")) {
                    sqlBuilder.append(" AND ur.group_id =:userGrpId");
                    sqlBuilder.append(" AND ur.full_Name LIKE :fullName");
                } else {
                    if (fullName != null && fullName != "") {
                        sqlBuilder.append(" AND ur.full_Name LIKE :fullName");
                    }
                    if (groupId > 0) {
                        sqlBuilder.append(" AND ur.group_id =:userGrpId");
                    }
                }
                Query query = session.createSQLQuery(sqlBuilder.toString())
                .addScalar("user_id", Hibernate.INTEGER).addScalar(
                        "login_name", Hibernate.STRING).addScalar(
                        "full_name", Hibernate.STRING).addScalar(
                        "full_name_kana", Hibernate.STRING).addScalar(
                        "email", Hibernate.STRING).addScalar(
                        "tel", Hibernate.STRING).addScalar(
                        "birthday", Hibernate.DATE).addScalar(
                        "group_name", Hibernate.STRING).addScalar(
                        "name_level", Hibernate.STRING).addScalar(
                        "start_date", Hibernate.DATE).addScalar(
                        "end_date", Hibernate.DATE)
                .setResultTransformer(
                        Transformers.aliasToBean(UserDetailDto.class));

                query.setFirstResult(offset);
                query.setMaxResults(limit);
                if (groupId > 0 && (fullName != null && fullName != "")) {
                    query.setString("userGrpId", String.valueOf(groupId));
                    query.setString("fullName", "%" + fullName + "%");
                } else {
                    if (fullName != null && fullName != "") {
                        query.setString("fullName", "%" + fullName + "%");
                    }
                    if (groupId > 0) {
                        query.setString("userGrpId", String.valueOf(groupId));
                    }
                }
                List<UserDetailDto> list = (List<UserDetailDto>) query.list();
                return list;
            }
        };
        List<UserDetailDto> execute = (List<UserDetailDto>) getHibernateTemplate().execute(callback);
        return execute;
    }

}
